#!/usr/bin/evn python
# -*- encoding: utf-8 -*-

import os
import sys
import pytz
import xlrd
import xlwt
import time
import json
import MySQLdb
import datetime
import xlutils.copy
import urllib, urllib2

class Attendance:
    SQL = "SELECT userId, username FROM  communion.user "

    def __init__(self, host='', username='', password='', charset='utf8'):
        self.host = host
        self.username = username
        self.password = password
        self.charset = charset

        self.dbconn = None
        self.cursor = None

        self.users = {}
        self.attendances = {}
        self.checkins = {}

    def openDBConnect(self):
        self.dbconn = MySQLdb.connect(self.host, self.username, self.password, charset=self.charset)

    def getDBCursor(self):

        if not self.dbconn:
            self.openDBConnect()

        self.cursor = self.dbconn.cursor()

    def closeDBConnect(self):
        if self.dbconn:
            self.dbconn.close()
            self.dbconn = None

    def post(self, url, args, local=True):
        if local:
            req = urllib2.Request(url=url, data=urllib.urlencode(args))  # urllib.urlencode(args) / json.dumps(args)
            return json.loads(urllib2.urlopen(req).read())
        else:
            header = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko',"Content-Type": "application/json"}
            req = urllib2.Request(url=url, data=json.dumps(args),headers=header)  # urllib.urlencode(args) / json.dumps(args)
            return json.loads(urllib2.urlopen(req).read())

    def formatData(self, time):
        data = datetime.datetime.strptime(time, '%Y-%m-%d %H:%M:%S')
        return data

    def mkdir(self,path):

        isExists = os.path.exists(path)

        if not isExists:
            os.makedirs(path)
            # print path + u' 创建成功'
            return True
        else:
            # print path + u' 目录已存在'
            return False

    def wokerTypeJudgment(self, timeResult):

        strTimeResult = u""

        if timeResult == "Normal":
            strTimeResult = u"正常"

        if timeResult == "Early":
            strTimeResult = u"早退"

        if timeResult == "Late":
            strTimeResult = u"迟到"

        if timeResult == "SeriousLate":
            strTimeResult = u"严重迟到"

        if timeResult == "Absenteeism":
            strTimeResult = u"旷工迟到"

        if timeResult == "NotSigned":
            strTimeResult = u"未打卡"

        return strTimeResult

    def getAccessToken(self):
        url = 'http://114.215.183.148//queryDAccessTokenForNew.do?company=101'
        res = self.post(url, {})

        if res.has_key("errorCode") and res.get("errorCode") == 0:
            return res['dAccessToken']['accesstoken']
        else:
            return ''

    def getUser(self, sql):

        self.getDBCursor()

        self.cursor.execute(sql)

        _users = self.cursor.fetchall()

        self.closeDBConnect()

        for i in xrange(len(_users)):

            _user = _users[i]

            self.users[_user[0]] = {"name": _user[1]}

    def getAttendance(self, userIds, startTime, daynum, accessToken):

        url = "https://oapi.dingtalk.com/attendance/list?access_token=%s" % accessToken

        args = {
            "userIdList": userIds,
            "offset": 0,
            "limit": 50
        }

        records = []

        if len(userIds) == 0:
            return

        for i in xrange(daynum):
            _startTime = startTime + datetime.timedelta(days=i)
            _endTime = startTime + datetime.timedelta(days=i + 1) - datetime.timedelta(seconds=1)

            args["workDateFrom"] = str(_startTime)
            args["workDateTo"] = str(_endTime)

            res = self.post(url, args, False)

            if res.has_key("errcode") and res.get("errcode") == 0:
                records = records + res.get("recordresult")

        for record in records:

            userId = record.get("userId")
            workdate = time.strftime("%y%m%d", time.localtime(record.get("workDate") / 1000))
            checktype = record.get("checkType")
            timeresult = record.get("timeResult")

            if not self.users.has_key(userId):
                user = self.users[userId] = {"name": ""}
            else:
                user = self.users[userId]

            if not user.has_key(workdate):
                check = user[workdate] = ["", ""]
            else:
                check = user.get(workdate)

            if checktype == "OnDuty":
                check[0] = timeresult
            else:
                check[1] = timeresult

            if timeresult != "Normal":

                if self.checkins.has_key(workdate):
                    _checkins = self.checkins.get(workdate)

                    if userId not in _checkins:
                        self.checkins.get(workdate).append(userId)
                else:
                    self.checkins[workdate] = [userId]

    def attendance(self, startTime, daynum, users, accessToken):

        userIds = users.keys()

        for i in xrange(0, len(users.keys()), 25):

            self.getAttendance(userIds[i: i + 25], startTime, daynum, accessToken)

        for date in self.checkins.keys():

            userIds = self.checkins.get(date)

            for i in xrange(0, len(userIds), 10):
                self.checkin(date, userIds[i: i + 10], accessToken, 0)

    def checkin(self, date, userIds, accessToken, cursor):

        _startTime = int(time.mktime(time.strptime("%s040000" % date, "%y%m%d%H%M%S"))) * 1000
        _endTime = int(time.mktime(time.strptime("%s220000" % date, "%y%m%d%H%M%S"))) * 1000

        args = {
            "userid_list": ",".join(userIds),
            "start_time": _startTime,
            "end_time": _endTime,
            "cursor": cursor,
            "size": 100,
            "method": "dingtalk.smartwork.checkin.record.get",
            "session": accessToken,
            "timestamp": time.strftime("%Y-%m-%d %H:%M:%S"),
            "v": "2.0",
            "format": "json",
            "simplify": False
        }

        url = "https://eco.taobao.com/router/rest"

        res = self.post(url, args, True)

        response = res.get("dingtalk_smartwork_checkin_record_get_response")
        result = response.get("result")
        pagelist = result.get("result").get("page_list")

        if pagelist.has_key("checkin_record_vo"):
            records = pagelist.get("checkin_record_vo")
            for i in records:

                userId = i.get("userid")

                if self.users.has_key(userId):
                    user = self.users[userId]
                    checkinTime = datetime.datetime.fromtimestamp(int(i.get("checkin_time") / 1000),pytz.timezone('Asia/Shanghai'))

                    _date = checkinTime.strftime("%y%m%d")
                    if user.has_key(_date):
                        _info = user.get(_date)
                        if checkinTime.strftime("%H%M") < "0930":
                            _info[0] = "Normal"
                        if checkinTime.strftime("%H%M") > "1700":
                            _info[1] = "Normal"

        if result.has_key("next_cursor"):
            cursor = result.get("next_cursor")
            self.checkin(date, userIds, accessToken, cursor)

    def readout(self,ymd):

        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet(str(ymd), cell_overwrite_ok=True)

        sheet.write(0, 0, u"工号")
        sheet.write(0, 1, u"姓名")
        sheet.write(0, 2, u"班次")

        for field in range(3, 34):
            sheet.write(0, field, str(field - 2) + u"号")

        Y="20" + ymd[0:2]
        M=ymd[2:4]
        D=ymd[4:6]

        _path = u"/home/fanglin/cronbin/attendance/%s%s" % (Y,M)
        self.mkdir(_path)               #检测目录是否存在,不存在将创建

        path = u"/home/fanglin/cronbin/attendance/%s%s/%s.xls" %(Y,M,ymd)
        workbook.save(path)


        book = xlrd.open_workbook(path)
        wtbook = xlutils.copy.copy(book)
        sheet = wtbook.get_sheet(0)

        crow=1
        user = self.users
        for i,v in enumerate(user):

            if user[v].has_key(ymd):
                commute =user[v][ymd]
                shangban=self.wokerTypeJudgment(commute[0])
                xiaban=self.wokerTypeJudgment(commute[1])

                if shangban!="" or xiaban !="":

                    if user.has_key(v):
                        userName = user[v]["name"]
                    else:
                        userName = ""

                    sheet.write(crow, 0, u'%s' % v)
                    sheet.write(crow, 1, u'%s' % userName)

                    pattern = xlwt.Pattern()
                    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
                    pattern.pattern_fore_colour = 22
                    if shangban!=u"正常" :
                        pattern.pattern_fore_colour = 5
                    style = xlwt.XFStyle()
                    style.pattern = pattern
                    sheet.write(crow, int(D) + 2, u'%s' % (shangban), style)

                    patterns = xlwt.Pattern()
                    patterns.pattern = xlwt.Pattern.SOLID_PATTERN
                    patterns.pattern_fore_colour = 22
                    if xiaban!=u"正常":
                        patterns.pattern_fore_colour = 5
                    styles = xlwt.XFStyle()
                    styles.pattern = patterns
                    sheet.write(crow+1, int(D)+2, u'%s' %(xiaban), styles)

                    sheet.write(crow, 2, u'上班')
                    sheet.write(crow+1, 2, u'下班')

                    crow = crow + 2



        wtbook.save(path)

    def time(self, startTime, endTime, num=31):
        if type(startTime) != datetime.datetime:
            if startTime.find(u"/") != -1:
                startTime = startTime.replace("/", "-")
                endTime = endTime.replace("/", "-")

            if len(startTime) == 10:
                startTime = "%s 00:00:00" % startTime
                endTime = "%s 23:59:59" % endTime

            startTime = datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S')
            endTime = datetime.datetime.strptime(endTime, '%Y-%m-%d %H:%M:%S')

        if endTime - startTime <= datetime.timedelta(days=num):
            return True
        else:
            return False

    def main(self, startTime, endTime):

        falg = self.time(startTime, endTime)
        if falg == False:
            raise RuntimeError(u'时间范围不在1-31天之内')

        accessToken = self.getAccessToken()

        _startTime = self.formatData("%s 00:00:00" % startTime)
        _endTime = self.formatData("%s 23:59:59" % endTime)

        daynum = (_endTime - _startTime).days + 1

        self.getUser(self.SQL)

        count=0
        countStartTime = _startTime
        while(count<daynum):
            ymd =str(countStartTime.strftime('%y%m%d'))

            self.attendance(countStartTime, 1, self.users, accessToken)
            countStartTime = countStartTime + datetime.timedelta(days=1)

            self.readout(ymd)
            count = count +1



if __name__ == '__main__':
    """
     1.脚本说明:
        按照指定日期时间范围查询人员考勤和签到记录,整理后导出全部人员每天的考勤数据,每天一个Excel;
        例:导出的Excel路径为/home/fanglin/cronbin/attendance/201806/180625.xml 如果目录不存在将自动创建
        注:获取签到数据时间为, 早04:00:00 -- 晚22:00:00
           签到时间在,早09:30:00之前 -- 晚17:00:00之后 修改考勤数据为正常
     3.例:
        1.python crond_dttendance_collect.py <自动获取昨天时间>
          python crond_dttendance_collect.py auto
          
        2.python crond_dttendance_collect.py <手动输入时间><开始时间><结束时间>
          python crond_dttendance_collect.py noauto 2018-06-01 2018-06-05
          
     4.导出结果:
         员工号    班次      1号      2号       ....
        FL-0001  上下班  正常,迟到  正常,正常     ....
        FL-0002  上下班  正常,正常  正常,正常     ....
    """

    config = json.load(open("config.json"))
    dbconf = config.get("database")

    attendance = Attendance(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"))

    action = sys.argv[1]

    if action == "auto":
        nowTime = datetime.datetime.now()
        yesterday = nowTime - datetime.timedelta(days=1)
        yesterdayStr = yesterday.strftime("%Y-%m-%d")
        attendance.main(yesterdayStr, yesterdayStr)
    else:
        attendance.main(sys.argv[2], sys.argv[3])

    print u"程序执行完毕!!!"